<?php

/**
 * Created by PhpStorm.
 * User: qiliping
 * Date: 2016/12/7
 * Time: 上午10:52
 */
class User_center_model extends CI_Model
{
	
  /**
  **  查询用户订单
  */
	public function get_order_count_by_status($uid,$status='')
	{
		$school_id = (int)$this->school_id;
		$where = "buyer_user_id = '{$uid}' and school_id = ".$school_id;
		if(!empty($order_type))
		{
		  $where .= " AND order_type = {$order_type} ";
		}
		if(is_numeric($status))
		{
			if($status>-1)
			{
			  $where .= " AND order_status = {$status} ";
			}
		}
		$row_num =  $this->db->query("SELECT count(order_id) as row_num from {$this->db->dbprefix}shopping_orders where $where")->row_array();
		return $row_num['row_num'];
	}  
	/**
	  **  查询用户订单
	  */
	public function get_order_list_by_uid($limit = 0, $offset = 10,$uid,$data = false)
	{
		$where = "buyer_user_id = '{$uid}' ";
		if(!empty($data['order_type']))
		{
		  $where .= " AND o.order_type = {$data['order_type']} ";
		}
		if($data['status']>-1)
		{
		  $where .= " AND o.order_status = {$data['status']} ";
		}
		if($data['begin_time'])
		{
			$data['begin_time']=strtotime($data['begin_time']);
			$where .= " AND o.order_time >= {$data['begin_time']} ";
		}
		
		if($data['end_time'])
		{
			$data['end_time']=strtotime($data['end_time']) + 3600 * 24;
			$where .= " AND o.order_time <= {$data['end_time']} ";
		}
		
		if(!empty($data['search_type']) && $data['search_type']==1 && !empty($data['order_num'])) 
		{
			$where .= " AND o.order_num like '%".$data['order_num']."%'";
		}
		if(!empty($data['search_type']) && $data['search_type']==2 && !empty($data['order_num'])) 
		{
			$where .= " AND o.buyer_user_name like '%".$data['order_num']."%'";
		}
		if(!empty($data['search_type']) && $data['search_type']==3 && !empty($data['order_num'])) 
		{
			$where .= " AND o.buyer_user_phone like '%".$data['order_num']."%'";
		}

		$this->db->trans_start();
		$sql = "SELECT SQL_CALC_FOUND_ROWS o.*,cam.name as campus_name FROM {$this->db->dbprefix}shopping_orders o left join {$this->db->dbprefix}o2o_school_campus cam on cam.id = o.school_campus_id where $where order by o.order_time desc LIMIT $limit, $offset ;";
		$result = $this->db->query($sql)->result_array();
		$order_ids=false;
		$order_item=false;
		if($result)
		{
			foreach($result as $key =>$val)
			{
				$order_ids[]=$val['order_id'];
			}
		}
		if($order_ids)
		{
			$order_item=$this->get_order_item_list_by_order_id($order_ids);
		}
		if($result)
		{
			foreach($result as $key =>$val){
				if($order_item)
				{
					$result[$key]['items']=$order_item[$val['order_id']];
				}
			}
		}
		
		$sql = "SELECT count(o.order_id) as row_num FROM {$this->db->dbprefix}shopping_orders o where $where ;";
		$row_num = $this->db->query($sql)->row_array();
		return array("total_count" => $row_num['row_num'], "result" => $result);
	}
	
	/**
	 * 获取课程分类，默认不取面授课分类
	 * @param  integer $type [description]
	 * @return [type]        [description]
	 */
	function get_course_cate($type=0){
		//$and_where = "1=1 and school_id =  ".(int)$this->user_data['school_id'];
		$and_where = "1=1 ";
		if($type==0){
			$and_where .= " and type!=2";//不取面授课分类
		}else{
			$and_where .= " and type=$type";
		}
		$sql = "select * from {$this->db->dbprefix}course_cate where  $and_where";
		$result = $this->db->query($sql)->result_array();
		return $result;
	}
	
	function get_course_cate_subject($id)
	{
		$id = (int)$id;
		$sql = "SELECT * from {$this->db->dbprefix}course_cate_subject where pid = $id ";
		$results = $this->db->query($sql)->result_array();
		return $results;
	}
	/**
	* 函数功能介绍：返回课程列表（用于分页显示）
	* 创建人:  Yang Ningshu
	* @param id int
	* @return array
	*/
	function get_anchors_course_list($limit = 0, $offset = 10,$data = 0){
		$where = "it.uid=".$this->user_data['id'];
		$order = "v.id desc";

		if($data['status'] != 10){
			$where .= " AND v.status = '". $data['status']."'";
		}
		//1：视频课程  2：直播课程3：班课
		if($data['type'] > 0){
			$where .= " AND v.type = '". $data['type']."'";
		}

		if($data['cate_id'] && $data['cate_id'] > 0){
			$where .= " AND v.cate_id = '". $data['cate_id']."'";
		}
		
		if($data['kemu'] && $data['kemu'] > 0){
			$where .= " AND v.subject_id = '". $data['kemu']."'";
		}
		
		if(!empty($data['search'])) {
			$where .= " AND v.title like '%".$data['search']."%'";
		}

		$sql = "SELECT SQL_CALC_FOUND_ROWS v.uid,v.id,v.title,v.course_img,u.is_live,u.is_push,u.start_time as live_start_time,ot.teacher_name,it.lose_time FROM {$this->db->dbprefix}course AS v ";
		$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_student_item_terms AS it ON(it.data_id=v.id AND it.data_type = v.type) ";
		$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_teachers AS ot ON (v.teacher_id = ot.id) ";
		$sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON (u.id = v.uid) ";
		$sql .= "WHERE $where ORDER BY $order LIMIT $limit, $offset ";
		$result = $this->db->query($sql)->result_array();
		$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
      	return array("count" => $row_num['row_num'], "result" => $result);


		/*$sql = "SELECT v.uid,v.id,v.title,v.course_img,u.is_live,u.is_push,u.start_time as live_start_time,sc.effective_time as student_course_effective_time,sc.add_time as student_course_add_time,ot.teacher_name,sc.status as student_course_status FROM {$this->db->dbprefix}o2o_student_course as sc 
		left join {$this->db->dbprefix}course as v on(v.id = sc.course_id ) 
		left join {$this->db->dbprefix}course_shelves as cs on(v.id = cs.course_id ) 
		left join {$this->db->dbprefix}o2o_teachers ot on v.teacher_id = ot.id 
		left join {$this->db->dbprefix}user u on u.id = sc.user_id 
		WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
		$result = $this->db->query($sql)->result_array();
		if($data['search']){
			foreach ($result as $key => $value) {
				$result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
			}
		}
		$sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}course as v 
		left join {$this->db->dbprefix}o2o_student_course as sc on(v.id = sc.course_id and v.school_id = sc.school_id ) 
		WHERE $where";
		$count = $this->db->query($sql)->result();
		return array("count" => $count[0]->count, "result" => $result);*/
	}
	
	/**
	* 函数功能介绍：返回视频列表（用于分页显示）
	* 创建人:  Yang Ningshu
	* 创建时间: 2014-08-21 11:08
	* 最后修改人：Zhang ShaoWei
	* 最后修改时间：2014-08-29 15:08
	* @param id int
	* @return array
	*/
	function get_video_course_list($limit = 0, $offset = 10,$data = 0){
		$where = "1=1";
		if($data['sort'] == 1 and $data['asc_des'] == 2){
			$order = "v.baidu_recommended asc";
		}else if($data['sort'] == 1 and $data['asc_des'] == 1){
			$order = "v.baidu_recommended desc";
		}else if($data['sort'] == 2 and $data['asc_des'] == 2){
			$order = "v.click_num asc";
		}else if($data['sort'] == 2 and $data['asc_des'] == 1){
			$order = "v.click_num desc";
		}else if($data['sort'] == 3 and $data['asc_des'] == 2){
			$order = "v.wap_recommended asc";
		}else if($data['sort'] == 3 and $data['asc_des'] == 1){
			$order = "v.wap_recommended desc";
		}else if($data['sort'] == 4 and $data['asc_des'] == 2){
			$order = "v.add_time asc";
		}else if($data['sort'] == 4 and $data['asc_des'] == 1){
			$order = "v.add_time desc";
		}else if($data['sort'] == 5 and $data['asc_des'] == 2){
			$order = "v.status asc";
		}else if($data['sort'] == 5 and $data['asc_des'] == 1){
			$order = "v.status desc";
		}else if($data['sort'] == 6 and $data['asc_des'] == 2){
			$order = "v.sort asc";
		}else if($data['sort'] == 6 and $data['asc_des'] == 1){
			$order = "v.sort desc";
		}else{
			$order = "v.id desc";
		}
		if(isset($data['aid'])){
			$sql = "SELECT video_id FROM {$this->db->dbprefix}activity  WHERE id=".$data['aid']."";
			$result = $this->db->query($sql)->result_array();
			if($result[0]['video_id']){
				$where.=" and v.id in (".$result[0]['video_id'].")";
			}else{
				$result[0]['video_id'] = 0;
				$where.=" and v.id in (".$result[0]['video_id'].")";
			}
		}

		if($data['review'] && $data['review'] > 0){
			$where .= " AND v.review = '". $data['review']."'";
		}
		
		if($data['cate_id'] && $data['cate_id'] > 0){
			$where .= " AND v.cate_id = '". $data['cate_id']."'";
		}
		
		if(!empty($data['search'])) {
			$where .= " AND v.title like '%".$data['search']."%'";
		}

		$sql = "SELECT v.id,v.cate_id,v.uid,v.video_size,v.image_url,v.review,v.duration,v.title,v.click_num,v.add_time,v.sort,v.status, u.nick FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
		$result = $this->db->query($sql)->result_array();
		if($data['search']){
			foreach ($result as $key => $value) {
				$result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
			}
		}
		$sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid WHERE $where";
		$count = $this->db->query($sql)->result();
		return array("count" => $count[0]->count, "result" => $result);
	}
	
	/**
	  * 获取学校班级
	*/
	public function get_class_by_uid($uid)
	{
		$sql = "SELECT oc.*,cam.name as school_name,room.name as class_room FROM {$this->db->dbprefix}o2o_class oc ".
		" left join {$this->db->dbprefix}o2o_school_campus cam on cam.id = oc.campus ".
		" left join {$this->db->dbprefix}o2o_classroom room on room.id = oc.classroom ".
		" left join {$this->db->dbprefix}o2o_class_student ocs on ocs.class_id = oc.id ".
		" left join {$this->db->dbprefix}o2o_student os on ocs.student_id = os.student_id ".
		" where os.user_id = '{$uid}' order by id desc";
		return $this->db->query($sql)->result_array();
	}
	  
	/**
	   * 根据id获取班级详情
	   * @param  [type] $class_id [description]
	   * @return [type]           [description]
	   */
	public function get_class_by_id($class_id){
		$sql = "SELECT * FROM {$this->db->dbprefix}o2o_class where id = '{$class_id}' limit 1";
		return $this->db->query($sql)->row_array();
	}
	/**
   * 获取班级学员
   * @param  [type] $class_id [description]
   * @return [type]            [description]
   */
	public function get_student_by_class($class_id)
	{
		$sql = "select cstu.*,stu.*,user.head_image_url from {$this->db->dbprefix}o2o_class_student cstu ".
		" left join {$this->db->dbprefix}o2o_student stu on stu.student_id = cstu.student_id ".
		" left join {$this->db->dbprefix}user user on user.id = stu.user_id ".
		" where cstu.class_id = {$class_id} order by cstu.id desc";

		return $this->db->query($sql)->result_array();
	} 
	/**
	   * 根据uid获取班级作业
	   * @param  [type] $class_id [description]
	   * @return [type]           [description]
	   */
	public function get_homework_by_uid($uid){
		$sql = "select och.*,stu.*,user.head_image_url from {$this->db->dbprefix}o2o_class_homework och ".
		" left join {$this->db->dbprefix}o2o_class_homework_content ochc on ochc.home_work_id = och.id ".
		" left join {$this->db->dbprefix}o2o_class_homework_result ochr on ochr.home_work_id = och.id ".
		" left join {$this->db->dbprefix}o2o_student stu on stu.user_id = ochr.studer_user_id ".
		" left join {$this->db->dbprefix}o2o_class_student ocs on ocs.student_id = stu.student_id ".
		" left join {$this->db->dbprefix}user user on user.id = stu.user_id ".
		" where ochr.studer_user_id = {$uid} order by och.id desc";

		return $this->db->query($sql)->result_array();
	}
	
	/**
	   * 根据uid获取我的提问
	   * @param  [type] $class_id [description]
	   * @return [type]           [description]
	   */
	public function get_my_questions($uid){
		$sql = "select q.*,stu.*,user.head_image_url from {$this->db->dbprefix}questions q ".
		" left join {$this->db->dbprefix}o2o_school os on os.id = q.school_id ".
		" left join {$this->db->dbprefix}question_topics qt on qt.id = q.tid ".
		" left join {$this->db->dbprefix}o2o_student stu on stu.user_id = q.uid ".
		" left join {$this->db->dbprefix}o2o_class_student ocs on ocs.student_id = stu.student_id ".
		" left join {$this->db->dbprefix}user user on user.id = stu.user_id ".
		" where q.uid = {$uid} order by och.id desc";

		return $this->db->query($sql)->result_array();
	}
	
	/**
	   * 根据uid获取我的回答
	   * @param  [type] $class_id [description]
	   * @return [type]           [description]
	   */
	public function my_answers($uid){
		$sql = "select q.*,stu.*,user.head_image_url from {$this->db->dbprefix}question_answers qa".
		" left join {$this->db->dbprefix}questions q  on q.id = qa.qid ".
		" left join {$this->db->dbprefix}o2o_school os on os.id = q.school_id ".
		" left join {$this->db->dbprefix}question_topics qt on qt.id = q.tid ".
		" left join {$this->db->dbprefix}o2o_student stu on stu.user_id = q.uid ".
		" left join {$this->db->dbprefix}o2o_class_student ocs on ocs.student_id = stu.student_id ".
		" left join {$this->db->dbprefix}user user on user.id = stu.user_id ".
		" where qa.uid = {$uid} order by och.id desc";

		return $this->db->query($sql)->result_array();
	}
	
	/**
	   * 根据uid获取我的回答
	   * @param  [type] $class_id [description]
	   * @return [type]           [description]
	   */
	public function my_question_follow($uid){
		$sql = "select q.*,stu.*,user.head_image_url from {$this->db->dbprefix}question_follow qf".
		" left join {$this->db->dbprefix}questions q  on q.id = qf.qid ".
		" left join {$this->db->dbprefix}o2o_school os on os.id = q.school_id ".
		" left join {$this->db->dbprefix}question_topics qt on qt.id = q.tid ".
		" left join {$this->db->dbprefix}o2o_student stu on stu.user_id = q.uid ".
		" left join {$this->db->dbprefix}o2o_class_student ocs on ocs.student_id = stu.student_id ".
		" left join {$this->db->dbprefix}user user on user.id = stu.user_id ".
		" where qf.uid = {$uid} order by och.id desc";

		return $this->db->query($sql)->result_array();
	}

	public function get_order_item_list_by_order_id($order_ids = array())
	{
		$where = "order_id in(".join(',',$order_ids).") ";
		$sql = "SELECT * FROM {$this->db->dbprefix}shopping_order_item where $where ";
		$temp_result = $this->db->query($sql)->result_array();
		if($temp_result){
			foreach($temp_result as $key =>$val){
				$result[$val['order_id']][]=$val;
			}
		}
		unset($temp_result);
		return $result;
	}
}
